#!/usr/bin/env bash
#################################################
###  基础表:       专业概况统计
###  维护人:       王浩
###  数据源:       college_quality_info,major_trainingRoom_detailed,student_graduate_employment_record,
#student_job_orientation,semester_year_report,basic_major_info,basic_student_info,
#basic_semester_info

###  导入方式:      全量导入
###  运行命令:      sh app_table_data_source.sh. &
###  结果目标:      app.app_table_data_source
#################################################
cd `dirname $0`
source ../../../config.sh
source ../../basic/app/app_table_data_source.sh
exec_dir major_statistics_info

HIVE_DB=app
HIVE_TABLE=major_statistics_info
TARGET_TABLE=major_statistics_info
MODEL_DB="college_quality_info,major_trainingRoom_detailed,student_graduate_employment_record,
student_job_orientation,semester_year_report,basic_major_info,basic_student_info,
basic_semester_info"

function create_table() {
    hadoop fs -rm -r ${BASE_HIVE_DIR}/${HIVE_DB}/${HIVE_TABLE} || :
    hive -e "DROP TABLE IF EXISTS ${HIVE_DB}.${HIVE_TABLE};"
    hive -e "CREATE EXTERNAL TABLE IF NOT EXISTS ${HIVE_DB}.${HIVE_TABLE}(
	  semester_year STRING  COMMENT '学年 YYYY-YYYY',
	  teaching_resouce_num INT  COMMENT '专业教学资源库数量',
	  open_course_num INT  COMMENT '精品在线开放课程数量',
	  train_num INT  COMMENT '实训基地数量',
	  student_avg_value FLOAT  COMMENT '生均仪器设备值  单位：万元',
	  job_rate FLOAT  COMMENT '就业率',
	  country_num INT COMMENT '国家示范专业数',
	  core_num INT  COMMENT '骨干专业数',
	  firsht_num INT  COMMENT '一流专业数',
	  key_num INT  COMMENT '重点专业数',
	  reform_num INT  COMMENT '专业综合改革试点专业数',
	  major_amount INT  COMMENT '专业总数',
	  create_time STRING  COMMENT '创建时间'

    ) COMMENT '专业概况统计'
    LOCATION '${BASE_HIVE_DIR}/${HIVE_DB}/${HIVE_TABLE}'"
    fn_log "创建表——专业概况统计：${HIVE_DB}.${HIVE_TABLE}"
}

function import_table() {
    hive -e "
         INSERT INTO TABLE ${HIVE_DB}.${HIVE_TABLE}
            select
            '${SEMESTER_YEAR}' as semester_year,
            a.teaching_resouce_num as teaching_resouce_num,
            a.online_course_count as open_course_num,
            b.train_num as train_num,
            b.student_avg_value as student_avg_value,
            c.job_rate as job_rate,
            d.country_num as country_num,
            a.core_num as core_num,
            a.firsht_num as firsht_num,
            d.key_num as key_num,
            d.reform_num as reform_num,
            e.major_amount as major_amount,
            FROM_UNIXTIME(UNIX_TIMESTAMP()) AS create_time
            from
            (select
            COALESCE(cast(sum(a.major_teach_resource) as int),0) as teaching_resouce_num,
            COALESCE(cast(sum(a.online_course_count) as int),0) as online_course_count,
            COALESCE(cast(sum(a.backbone_professional) as int),0) as core_num,
            COALESCE(cast(sum(a.first_class_professional) as int),0) as firsht_num
            from
            model.college_quality_info a
            where a.semester_year ='${SEMESTER_YEAR}'
            )a,
            (
            select
            count(training_base_id) as train_num,
            cast(sum(b.price_avg) as decimal(9,2)) as student_avg_value
            from
            model.major_trainingRoom_detailed b
            where b.semester_year ='${SEMESTER_YEAR}' and b.major_code!=''
            )b,
            (
            select
            cast(nvl(employment_num/graduation_num,0) as decimal(9,2)) as job_rate
            from
            (
            SELECT b.major_code as major_code,b.semester_year,count(1) as graduation_num from model.student_graduate_employment_record a left join app.basic_semester_student_info b on
            a.code=b.code GROUP BY b.major_code,b.semester_year ) t1
            left join
            (
            SELECT major_code,semester_year,count(1) as employment_num from model.student_job_orientation b
            GROUP BY major_code,semester_year) t2
            on t1.major_code=t2.major_code and t1.semester_year=t2.semester_year
            and  t1.semester_year='${SEMESTER_YEAR}'
            )c,
            (
            select
            COALESCE(cast(sum(d.country_num) as int),0) as country_num,
            COALESCE(cast(sum(d.key_num) as int),0) as key_num,
            COALESCE(cast(sum(d.reform_num) as int),0) as reform_num
            from
            model.semester_year_report d
            where
            d.semester_year ='${SEMESTER_YEAR}'
            )d,
            (
            select
            count(e.code) as major_amount
            from
            model.basic_major_info e
            where e.semester_year='${SEMESTER_YEAR}'
            )e
    "
    fn_log "导入数据 —— 专业概况统计：${HIVE_DB}.${HIVE_TABLE}"
}

function export_table() {
     clear_mysql_data "delete from ${TARGET_TABLE} where semester_year='${SEMESTER_YEAR}'"

    sqoop export --connect ${MYSQL_URL} --username ${MYSQL_USERNAME} --password ${MYSQL_PASSWORD} \
    --table ${TARGET_TABLE} --export-dir  ${BASE_HIVE_DIR}/${HIVE_DB}/${HIVE_TABLE} \
    --input-fields-terminated-by '\0001' --input-null-string '\\N' --input-null-non-string '\\N' \
    --null-string '\\N' --null-non-string '\\N' \
    --columns 'semester_year,teaching_resouce_num,open_course_num,train_num,student_avg_value,job_rate,country_num,core_num,firsht_num,
	key_num,reform_num,other_num,create_time'
    fn_log "导出数据--专业概况统计:${HIVE_DB}.${TARGET_TABLE}"
}

function getYearData(){
    vDate=`date +%Y`
    let vDate+=0;
    for((i=1;i<=3;i++));
    do
        let NOW_YEAR=vDate-i+1
        let PRE_YEAR=vDate-i
        create_table
        import_table
        export_table
    done
}

if [ $# == 1 ]
    then
    getYearData
    data_cleaning
    finish
else
    getYearData
    finish
fi